package com.scalar.chiptrack.adaptor.oset;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.adaptor.wipfed.WipFedStatus;
import com.scalar.chiptrack.adaptor.osep.OSEPWipStatus;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * Created by IntelliJ IDEA.
 * User: Sudhakar
 * Date: Jul 16, 2007
 * Time: 7:56:37 PM
 */
public class OSETDAO  implements AdaptorConstants
{

    public static void saveWipFedOSETDetails(DBTransaction dbTransaction, ArrayList wipList, boolean noLotsFlag ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        WipFedStatus wipStatus = null;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");

            if ( noLotsFlag )
            {
                // Delete all THE PREVIOUS DATA IN TABLE
                pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_OSET_ASSEMBLY_Table");
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
                success = true;
            }
            else
            {
                // Step 1
                // Delete all THE PREVIOUS DATA IN TABLE
                pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_OSET_ASSEMBLY_Table");
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                // Delete all THE PREVIOUS DATA IN HISTORY TABLE
                pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_OSET_ASSEMBLY_History_Table WHERE FILE_DATE = ?");
                pstmt.setString(1, currentDate);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement("{ call sp_save_wip_Fed_oset_status ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

                Iterator wipStatusIterator = wipList.iterator();
                while (wipStatusIterator.hasNext())
                {
                    wipStatus = (WipFedStatus) wipStatusIterator.next();

                    //log(wipStatus.toString());

                    if (wipStatus.getDevice() != null)
                    {
                        pstmt.setString(1, wipStatus.getLotNumber());
                        pstmt.setString(2, wipStatus.getDevice());
                        pstmt.setString(3, wipStatus.getStartDate());
                        pstmt.setString(4, wipStatus.getRsDate());
                        pstmt.setString(5, wipStatus.getOsDate());
                        pstmt.setString(6, wipStatus.getOutDate());
                        pstmt.setString(7, wipStatus.getMesQty());
                        pstmt.setString(8, wipStatus.getMesStep());
                        pstmt.setString(9, wipStatus.getPsType());
                        pstmt.setString(10, currentDate);
                        pstmt.addBatch();
                    }
                }

                pstmt.executeBatch();
                success = true;
            }
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not save WIP FED OSET Wip Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }


    public static void saveOSETAssemblyWipDetails(DBTransaction dbTransaction, ArrayList osetWipList) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        OSEPWipStatus osetStatus = null;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");
            // Step 1
            // Delete all THE PREVIOUS DATA IN TABLE
            pstmt = conn.prepareStatement("DELETE FROM OSET_WIP_TABLE");
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            // Delete all THE PREVIOUS DATA IN HISTORY TABLE
            pstmt = conn.prepareStatement("DELETE FROM OSET_WIP_HISTORY_TABLE WHERE file_date = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("{ call sp_save_oset_wip_info ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            Iterator lotEventHistoryIterator = osetWipList.iterator();
            while (lotEventHistoryIterator.hasNext())
            {
                osetStatus = (OSEPWipStatus) lotEventHistoryIterator.next();

                //log( tsmcShipList + "\n" );

                pstmt.setString(1, osetStatus.getDevice());
                pstmt.setString(2, osetStatus.getLotNo());
                pstmt.setString(3, osetStatus.getPkgType());
                pstmt.setInt(4, osetStatus.getAssemblyQtyIn());
                pstmt.setString(5, osetStatus.getDateCode());
                pstmt.setString(6, osetStatus.getPoNo());
                pstmt.setString(7, osetStatus.getStageName());
                pstmt.setString(8, osetStatus.getInputDate() );
                pstmt.setString(9, currentDate);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not save OSET WIP Information into database", sqle);
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }


    public static HashMap getOSETAssemblyMap() throws Exception
    {
        HashMap osetAssemblyMap     = new HashMap();
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        AssemblyLot assemblyLot     = null;
        String partNumber           = null;
        String lotNumber            = null;
        String pkgType              = null;
        String qtyIn                = null;
        String dateCode             = null;
        String poNumber             = null;
        String stageName            = null;
        String dateIn               = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(" SELECT part_number, lot_number, pkg_type, qty, date_code, po_number, stage_name, date_in \n" +
                    "FROM  OSET_WIP_TABLE \n" +
                    "UNION \n" +
                    "SELECT PART_NUMBER, LOT_NUMBER, null, qty, null, null, MES_STEP, Start_Date  \n" +
                    "FROM Wip_Fed_OSET_ASSEMBLY_Table" );

            rset = pstmt.executeQuery();

            while ( rset.next() )
            {

                partNumber  = rset.getString( "part_number" );
                lotNumber   = rset.getString( "lot_number" );
                pkgType     = rset.getString( "pkg_type" );
                qtyIn       = rset.getString( "qty" );
                dateCode    = rset.getString( "date_code" );
                poNumber    = rset.getString( "po_number" );
                stageName   = rset.getString( "stage_name" );
                dateIn      = rset.getString( "date_in" );


                if (lotNumber != null && partNumber != null)
                {
                    assemblyLot = (AssemblyLot) osetAssemblyMap.get(partNumber + lotNumber);
                    if (assemblyLot == null)
                    {
                        assemblyLot = new AssemblyLot();
                        osetAssemblyMap.put(partNumber + lotNumber, assemblyLot);
                    }
                    int quantity = 0;
                    if (assemblyLot.getQtyIn() != null)
                    {
                        try
                        {
                            quantity = Integer.parseInt(assemblyLot.getQtyIn());
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                        try
                        {
                            quantity += Integer.parseInt( qtyIn );
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                    }
                    else
                    {
                        try
                        {
                            quantity = Integer.parseInt( qtyIn );
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                    }

                    if( dateIn != null && dateIn.length() < 8 )
                    {
                        dateIn = dateIn + "/2007";
                    }

                    if( quantity > 0 )
                    {
                        assemblyLot.setLotNumber(lotNumber);
                        assemblyLot.setDeviceName(partNumber);
                        assemblyLot.setDateIn(dateIn);
                        assemblyLot.setQtyIn(quantity + "");
                        assemblyLot.setStage(stageName);
                        assemblyLot.setVendorName(VENDOR_NAME_OSET);
                        assemblyLot.setPONumber( poNumber );
                        assemblyLot.setCategory(LOT_CATEGORY_TYPE_PROD);
                        assemblyLot.setStatus(LOT_STATUS_RUNNING);
                        assemblyLot.setFabLotNumber( lotNumber );
                        assemblyLot.setDateCode( dateCode );
                        assemblyLot.setPackageType(pkgType);

                    }
                }
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all OSET Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return osetAssemblyMap;
    }
}
